# Use pgroll with ORMs

ORMs are popular tools to manage data schema from your application's code. They can translate objects from your code into database objects. Most ORMs can generate SQL migrations scripts. `pgroll` takes these generated statements and translates them into `pgroll` migrations. The generated migration can be saved to a file and you can run the `pgroll` migration using the `start` command as usual.

## How to convert SQL migration scripts

`pgroll` can translate SQL migration scripts generated by ORMs into pgroll migrations using the `convert` subcommand. It can read SQL statements from `stdin` or from a specified file.

```sh
$ pgroll convert --help
Convert SQL statements to a pgroll migration. The command can read SQL statements from stdin or a file

Usage:
  pgroll convert <path to file with migrations> [flags]
Flags:
  -h, --help          help for convert
```

## Examples

You can use `pgroll` with any ORM that can generate raw SQL statements for its migrations. Let's look at a few examples.

### Alembic

[Alembic](https://alembic.sqlalchemy.org/en/latest/) is a database migration tool used in SQLAlchemy projects. You can generate SQL statements from its migrations in [its offline mode](https://alembic.sqlalchemy.org/en/latest/offline.html). If you add
a `--sql` flag to the `upgrade` command of `alembic` it prints the SQL statements to stdout. You can pipe this output into `pgroll convert`:

```sh
$ alembic update {revision} --sql | pgroll convert
{
  "operations": [
    {
      "create_table": {
        "name": "employees",
        "colunms:" [
          {
            "name": "name",
            "type": "varchar(100)"
          },
          {
            "name": "joined",
            "type": "timestamp with time zone"
          },
          {
            "name": "email",
            "type": "varchar(254)"
          }
        ]
      }
    }
  ]
}
```

### Django

Django is the go-to tool for Python web development. It can connect to several databases, including PostgreSQL. If your web application uses PostgreSQL storage, you can leverage the `convert` subcommand.

After you've defined your migration in Python, you can use `manage.py` to extract the SQL script from Django. The [subcommand `sqlmigrate`](https://docs.djangoproject.com/en/5.1/ref/django-admin/#django-admin-sqlmigrate) prints
the SQL statements to stdout.

Create a new model for an `Employee` with a following simplified model:

```python
class Employee():
    name = models.CharField(max_length=100)
    joined = models.DateTimeField()
    email = models.EmailField()
```

Then run `sqlmigrate` to generate the SQL statements and pipe the output into `pgroll convert`:

```sh
manage.py sqlmigrate my_app 0000 | pgroll convert
{
  "operations": [
    {
      "create_table": {
        "name": "employees",
        "colunms:" [
          {
            "name": "name",
            "type": "varchar(100)"
          },
          {
            "name": "joined",
            "type": "timestamp with time zone"
          },
          {
            "name": "email",
            "type": "varchar(254)"
          }
        ]
      }
    }
  ]
}
```

### Drizzle

Drizzle is a popular ORM for Typescript projects. You can extract SQL statements using its [`generate` command](https://orm.drizzle.team/docs/drizzle-kit-generate).

The following example schema in Drizzle can be translated using `pgroll`:

```ts
import { pgTable, timestamp, varchar } from 'drizzle-orm/pg-core';

export const employees = pgTable('employees', {
  name: varchar({ length: 100 }),
  joined: timestamp({ withTimezone: true }),
  email: varchar({ length: 254 })
});
```

Run `pgroll convert` to get the appropriate pgroll migrations.

```sh
drizzle-kit generate --dialect postgresql --schema=./src/schema.ts --name=init
pgroll convert 0000_init.sql
{
  "operations": [
      "create_table": {
        "name": "employees",
        "colunms:" [
          {
            "name": "name",
            "type": "varchar(100)"
          },
          {
            "name": "joined",
            "type": "timestamp with time zone"
          },
          {
            "name": "email",
            "type": "varchar(254)"
          }
        ]
      }
    ]
  }
}
```

## Limitations

The functionality still has some limitations. The generated pgroll migrations must be edited to provide `up` and `down` data migrations manually. You can find more details about writing these migrations in [this guide](updown).

Furthermore, the SQL statements are not aggregated into single pgroll operations. Some ORMs add unique constraints in a different statement when they are creating a table with a unique column. This leads to more pgroll operations than necessary. You can resolve this manually by removing the unique constraint operation from the pgroll migration, and add it to the list of `constraints` of `create_table`.

```json
{
  "create_table": {
    "name": "employees",
    "columns": [
      {
        "name": "email",
        "varchar(254)"
      }
    ]
  }
},
{
  "create_constraint": {
    "name": "my_unique_email",
    "type": "unique",
    "columns": ["email"]
    "up": {
      "email": "TODO"
    }
  }
}

```

can be simplified to

```json
{
  "create_table": {
    "name": "employees",
    "columns": [
      {
        "name": "email",
        "varchar(254)"
      }
    ],
    "constraints": [
      {
        "name": "my_unique_email",
        "type": "unique",
        "columns": ["email"]
      }
    ]
  }
}
```

Also, the same applies to some cases when the ORM does the backfilling for new columns. For example, when you add a new column with a default value, an ORM produces these SQL statements:

```sql
ALTER TABLE "adults" ADD COLUMN "age" smallint DEFAULT 18 NULL CHECK ("age" >= 18);
ALTER TABLE "adults" ALTER COLUMN "level" DROP DEFAULT;

UPDATE adults SET age = 18;

ALTER TABLE adults ALTER COLUMN age SET NOT NULL;

UPDATE "adults" SET "age" = 18 WHERE "age" IS NULL;
SET CONSTRAINTS ALL IMMEDIATE;

ALTER TABLE "adults" ALTER COLUMN "age" SET NOT NULL;
```

Resulting in the following pgroll migration using the `convert` subcommand:

```json
{
  "add_column": {
    "column": {
      "check": {
        "constraint": "age >= 18",
        "name": "age_check"
      },
      "default": "18",
      "name": "age",
      "nullable": true,
      "type": "smallint"
    },
    "table": "adults",
    "up": "TODO: Implement SQL data migration"
  }
},
{
  "alter_column": {
    "column": "age",
    "default": null,
    "table": "adults",
    "down": "TODO: Implement SQL data migration",
    "up": "TODO: Implement SQL data migration"
  }
},
{
  "sql": {
    "up": "update adults set age = 18"
  }
},
{
  "alter_column": {
    "column": "age",
    "nullable": false,
    "table": "adults",
    "up": "TODO: Implement SQL data migration"
    "down": "TODO: Implement SQL data migration",
  }
},
{
  "sql": {
    "up": "UPDATE \"adults\" SET \"age\" = 18 WHERE \"age\" IS NULL"
  }
},
{
  "sql": {
    "up": "SET CONSTRAINTS ALL IMMEDIATE"
  }
},
{
  "alter_column": {
    "column": "age",
    "nullable": false,
    "table": "adults",
    "up": "TODO: Implement SQL data migration"
    "down": "TODO: Implement SQL data migration",
  }
}
```

This can be written as a single pgroll migration:

```json
{
  "add_column": {
    "column": {
      "check": {
        "constraint": "age \u003e= 0",
        "name": "age_check"
      },
      "default": "18",
      "name": "age",
      "nullable": true,
      "type": "smallint"
    },
    "table": "adults",
    "up": "18"
  }
},
```

Also, SQL migration scripts usually start with `BEGIN` and `COMMIT` because the ORM runs the DDLs in a single transaction. These statements show up in the list of operations in the generated migrations. These operations are safe to be deleted from the list.
